﻿#include "excelengine.h"
#include <QApplication>
#include "qt_windows.h"
#include <QTableWidget>
#include <QTableView>
#include <QTableWidgetItem>
#include <QDebug>
#include <QSql>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlRecord>
#include <QSqlResult>
#include <QSqlField>
#include <QDateTime>
#include <QThread>
#include <QProcess>
#include <QMessageBox>
#define SQLDATANAME "exceldata.db"
ExcelEngine::ExcelEngine(ExcelEngine::ExcelEngineType type, QString filename, QObject *parent)
	:QObject(parent)
{
	//old
	pExcel     = NULL;
	pWorkbooks = NULL;
	pWorkbook  = NULL;
	pWorksheet = NULL;

	sXlsFile     = "";
	nRowCount    = 0;
	nColumnCount = 0;
	nStartRow    = 0;
	nStartColumn = 0;

	bIsOpen     = false;
	bIsValid    = false;
	bIsANewFile = false;
	bIsSaveAlready = false;
	//end!
	QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
	QString dbn = QString("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};FIL={Excel xlsx};HDR=Yes;IMEX=1;Readonly=%1;DriverId=790;Dbq=%2;DefaultDir=e:\\");
	dbn = dbn.arg(QString::number((int)type),filename);
	m_dbnName = dbn;
	db.setDatabaseName(dbn);
	
	//mutex.lock();
 //   if (!db.open()){
	//	//if(db.lastError().number() ==0 ){//&& db.lastError().nativeErrorCode().contains("QODBC3: Unable to connect")){
	//	//	QMessageBox msgBox;
	//	//	msgBox.setText(QStringLiteral("数据库打开出错") + db.lastError().text());
	//	//	msgBox.setInformativeText("是否自动安装驱动?");
	//	//	msgBox.setStandardButtons( QMessageBox::Ok | QMessageBox::Cancel);
	//	//	msgBox.setDefaultButton(QMessageBox::Ok);
	//	//	int ret = msgBox.exec();
	//	//	mutex.unlock();
	//	//	if(ret)
	//	//		QProcess::startDetached(QApplication::applicationDirPath()+"/driver.exe",QStringList()<<"/passive");
	//	//	else
	//	//		return;
	//	//}
	//}
	//mutex.unlock();
	//db.close();
}

ExcelEngine::ExcelEngine(QObject *parent):
	QObject(parent)
{
	pExcel     = NULL;
	pWorkbooks = NULL;
	pWorkbook  = NULL;
	pWorksheet = NULL;

	sXlsFile     = "";
	nRowCount    = 0;
	nColumnCount = 0;
	nStartRow    = 0;
	nStartColumn = 0;

	bIsOpen     = false;
	bIsValid    = false;
	bIsANewFile = false;
	bIsSaveAlready = false;

	HRESULT r = OleInitialize(0);
	if (r != S_OK && r != S_FALSE)
	{
		qDebug("Qt: Could not initialize OLE (error %x)", (unsigned int)r);
	}
}

ExcelEngine::ExcelEngine(QString xlsFile, QObject *parent):
	QObject(parent)
{
	pExcel     = NULL;
	pWorkbooks = NULL;
	pWorkbook  = NULL;
	pWorksheet = NULL;

	sXlsFile     = xlsFile;
	nRowCount    = 0;
	nColumnCount = 0;
	nStartRow    = 0;
	nStartColumn = 0;

	bIsOpen     = false;
	bIsValid    = false;
	bIsANewFile = false;
	bIsSaveAlready = false;

	HRESULT r = OleInitialize(0);
	if (r != S_OK && r != S_FALSE)
	{
		qDebug("Qt: Could not initialize OLE (error %x)", (unsigned int)r);
	}

//    connect(this,SIGNAL(startSqlSearch(QString,QString)),this,SLOT(start()));
}

ExcelEngine::~ExcelEngine()
{
	if ( bIsOpen )
	{
		//析构前，先保存数据，然后关闭workbook
		Close();
	}
	OleUninitialize();
}

QPair<QMap<int,QString>,QStringList> ExcelEngine::ReadDataSheetByODBC(QString sheetName, QString groupName)
{
	QPair<QMap<int,QString>,QStringList> returnValue;
	returnValue.first = QMap<int,QString>();
	returnValue.second = QStringList();

	QMap<int,QString> map_boxValue;
	QSqlDatabase sqldb = dbcon();
	QSqlQuery query(sqldb);

	QDateTime startDate(QDate(1900, 1, 1),QTime(0,0,0));
//    QDateTime endDate = QDateTime(QDate(2014, 7, 6),QTime(0,0,0));//TODO:replace->QDateTime::currentDateTime();//(QDate(2013, 10, 1),QTime(0,0,0));
	QDateTime endDate = QDateTime::currentDateTime();
	qint64 realedays = startDate.daysTo(endDate) +2;//the reale day.

	qDebug()<<QStringLiteral("通过ODBC连接，当前时间为:") << endDate.toString("yyyy-MM-dd hh:mm:ss")<<QStringLiteral("Excel对应真实值:")<<realedays;

	if(!query.exec(QString("SELECT * FROM [%1$]").arg(sheetName))){
		qDebug()<<query.lastError()<<query.lastQuery()<<__LINE__;
		return returnValue;
	}
	int i_add_date = 0;
	QVariantList filedOfBoxNum;
	QStringList filedOfMatch;//filedOfBoxNum;
	while(query.next()){//row
		for(int i=0;i<query.record().count();i++){//column
			if(!i_add_date)
				if(query.value(i).toString().contains(QString::number(realedays)) ||
						(startDate.daysTo(query.value(i).toDateTime())+2) == realedays){
					i_add_date = i;
				break;
			}
			if(i_add_date){
				if(i == 2)
					filedOfBoxNum.append(query.value(2));
				if(i_add_date == i){

					QString strvalue = QString::number(query.value(i_add_date).toFloat());

					filedOfMatch.append(strvalue);

//qDebug()<<"Import data from excel the orig data:"<<strvalue<<strvalue.toFloat()*100.00;

					break;
				}
				else continue;
			}
		}
	}

	int startnum = 0;//should be charge the ap.
	QString valueOfDayAP;
	valueOfDayAP = QDateTime::currentDateTime().toString("ap");
	if(valueOfDayAP.contains("am"))
		valueOfDayAP = QStringLiteral("上午");
	else if(valueOfDayAP.contains("pm"))
			valueOfDayAP = QStringLiteral("下午");
	if(valueOfDayAP == QStringLiteral("上午")){
		startnum = 0;
	}else{
		startnum = 26;
	}

	qDebug()<<QStringLiteral("判断当前上下午:") << valueOfDayAP;

	if(startnum)
		for(int j=startnum;j<filedOfBoxNum.count();j++){
			QVariant var = filedOfBoxNum.at(j);
			if(filedOfBoxNum.at(j).toInt()){
				startnum = j;
				break;
			}
		}

	QStringList filedOfBoxNumStrlist;

	for(int i = startnum;i<filedOfMatch.count();i++){
		if(startnum==0 && filedOfMatch.at(i).toInt()>1000){
			break;
		}
		if(filedOfBoxNum.at(i).toInt()){
			map_boxValue.insert(filedOfBoxNum.at(i).toInt(),filedOfMatch.at(i));

			filedOfBoxNumStrlist.append(filedOfBoxNum.at(i).toString());
qDebug()<<"filedOfBoxNum:"<<filedOfBoxNum.count()<<filedOfBoxNum.at(i)<<filedOfMatch.at(i).toFloat()*100;
		}

//        qDebug()<<QStringLiteral("从Excel中获取得到数据:") << filedOfBoxNum.at(i).toInt()<<filedOfMatch.at(i)
//                  <<QStringLiteral("开始读取行号及总数：")<<startnum<<filedOfMatch.count();

	}

//    for(int itor = 0; itor < filedOfBoxNum.count(); itor++){


//    }

//    qCopy(filedOfBoxNum.begin(),filedOfBoxNum.end(),filedOfBoxNumStrlist);
	returnValue.first = map_boxValue;
	returnValue.second = filedOfBoxNumStrlist;
//    QMap<int,QString>::const_iterator i = map_boxValue.begin();
//    while (i != map_boxValue.end()) {
//        qDebug()<<"ReadDataSheetByODBC:"<<i.key()<<i.value()<<__LINE__;//filedOfMatch<<filedOfBoxNum<<QString::number(realedays)
//        ++i;
//    }

	sqldb.close();

	return returnValue;
    //    return map_boxValue;
}

QStringList ExcelEngine::GetSheetName()
{
    //"第(\\d*)餐"
    //QMutex m_mutex;
	QSqlDatabase sqldb = dbcon();
	
	QSqlQuery query(sqldb);
    //m_mutex.lock();
	//query.exec(QString::fromLatin1("select * from [第1餐投喂标准$] "));//where xtype = 'U'
	query.exec(QString("SELECT * FROM [%1$]").arg(QStringLiteral("第1餐投喂标准")));

    qDebug()<<"select_error:"<<query.lastError()<<query.lastQuery();
    while(query.next()){
        qDebug()<<"sheet:name:"<<query.value(0);
    }
	//m_mutex.unlock();
    return QStringList();
}

//投喂校验值
void ExcelEngine::WriteDataSheetByODBC(QString sheetName, QMap<int, QString>  map_boxValue)
{
	if(map_boxValue.isEmpty())return;

	QSqlDatabase sqldb = dbcon();
	QSqlQuery query(sqldb);

	QDateTime startDate(QDate(1900, 1, 1),QTime(0,0,0));
//    QDateTime endDate = QDateTime(QDate(2014, 7, 2),QTime(0,0,0));//TODO:replace->QDateTime::currentDateTime();//(QDate(2013, 10, 1),QTime(0,0,0));
	QDateTime endDate = QDateTime::currentDateTime();//(QDate(2013, 10, 1),QTime(0,0,0));
	qint64 realedays = startDate.daysTo(endDate) +2;//the reale day.

	//查找出对应行号与列
	if(!query.exec(QString("SELECT * FROM [%1$]").arg(sheetName))){
		qDebug()<<query.lastError()<<query.lastQuery()<<__LINE__;
		return;
	}
	int i_add_date = 0;//对应日期所在列号
	QVariantList filedOfBoxNum;//查找对应箱号顺序
//    QStringList filedOfMatch;//filedOfBoxNum;
//    int i_tmp = 0;
	while(query.next()){//row
		for(int i=0;i<query.record().count();i++){//column
			if(!i_add_date){
				if(query.value(i).toString().contains(QString::number(realedays)) ||
						(startDate.daysTo(query.value(i).toDateTime())+2) == realedays){
//qDebug()<<"findField:"<<query.value(i)<<query.value(i).toDateTime().toString("yyyy-MM-dd hh:mm:ss")<<query.record().count()<<i<<startDate.daysTo(query.value(i).toDateTime())+2;
					i_add_date = i;
					break;
				}
			}
			if(i_add_date){
				if(i == 2)
					filedOfBoxNum.append(query.value(2));
//                if(i_add_date == i){
//                    filedOfMatch.append(query.value(i_add_date).toString());
//                    break;
//                }
//                else continue;
			}
		}
	}
	int startnum = 0;//根据系统时间查找"开始行号"
	QString valueOfDayAP;
	valueOfDayAP = QDateTime::currentDateTime().toString("ap");
	if(valueOfDayAP.contains("am"))
		valueOfDayAP = QStringLiteral("上午");
	else if(valueOfDayAP.contains("pm"))
		valueOfDayAP = QStringLiteral("下午");
	if(valueOfDayAP == QStringLiteral("上午")){
		startnum = 0;
	}else{
		startnum = 25;
	}

	if(startnum)//如果是上午直接从0开始，下午则进行判断找出开始行号
		for(int j=startnum;j<filedOfBoxNum.count();j++){
			if(filedOfBoxNum.at(j).toInt()){
				startnum = j;
				break;
			}
		}
	QString filedNameFirst = query.record().fieldName(0);
	QString filedNameMatchDate = query.record().fieldName(i_add_date);
	QString filedNameBox = query.record().fieldName(2);

	//Update sql to Excel
	//查找要修改列的字段名
	if(i_add_date){
		for(int i = startnum;i<filedOfBoxNum.count();i++){
			QString updateValue = "0";
//            int mapFindValue = 0;
//            if(filedOfBoxNum.at(i).toInt()>1)
//                mapFindValue = filedOfBoxNum.at(i).toInt()-1;
			QMap<int,QString>::const_iterator itor = map_boxValue.find(filedOfBoxNum.at(i).toInt());
			if(itor != map_boxValue.end())
				updateValue = itor.value();

			query.exec(QString("UPDATE [%1$] SET [%2] = %4 WHERE [%3] = %5 and rowid < %6").arg(sheetName,
																				 filedNameMatchDate,
																				 filedNameBox,
																				 updateValue,
																				 filedOfBoxNum.at(i).toString(),
																				 QString::number(26)));

//qDebug()<<i<<startnum<<filedNameBox<<filedOfBoxNum.at(i).toString()<<query.lastError()<<query.lastQuery()<<updateValue<<":2222222222222222";
		}
	}
//    for(int i = startnum;i<filedOfMatch.count();i++){
//        if(startnum==0 && filedOfMatch.at(i).toInt()>1000){
//            break;
//        }
//        if(filedOfBoxNum.at(i).toInt())
//            map_boxValue.insert(filedOfBoxNum.at(i).toInt(),filedOfMatch.at(i));
//    }

//        QMap<int,QString>::const_iterator i = map_boxValue.begin();
//        while (i != map_boxValue.end()) {
//            qDebug()<<"ReadDataSheetByODBC:"<<i.key()<<i.value()<<__LINE__;//filedOfMatch<<filedOfBoxNum<<QString::number(realedays)
//            ++i;
//        }

	sqldb.close();
	return;
}

QSqlDatabase ExcelEngine::dbcon()
{
	QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
	db.setDatabaseName(m_dbnName);
	db.open();
	qDebug()<<"dbCon:"<<db.lastError();
	return db;
}

/**
  *@brief 打开sXlsFile指定的excel报表
  *@return true : 打开成功
  *        false: 打开失败
  */
bool ExcelEngine::Open(UINT nSheet, bool visible)
{

	if ( bIsOpen )
	{
		//return bIsOpen;
		Close();
	}

	nCurrSheet = nSheet;
	bIsVisible = visible;

	if ( NULL == pExcel )
	{
		pExcel = new QAxObject("Excel.Application");
		if ( pExcel )
		{
			bIsValid = true;
		}
		else
		{
			bIsValid = false;
			bIsOpen  = false;
			return bIsOpen;
		}

		pExcel->dynamicCall("SetVisible(bool)", bIsVisible);
	}

	if ( !bIsValid )
	{
		bIsOpen  = false;
		return bIsOpen;
	}

	if ( sXlsFile.isEmpty() )
	{
		bIsOpen  = false;
		return bIsOpen;
	}

	/*如果指向的文件不存在，则需要新建一个*/
	QFile f(sXlsFile);
	if (!f.exists())
	{
		bIsANewFile = true;
	}
	else
	{
		bIsANewFile = false;
	}

	if (!bIsANewFile)
	{
		pWorkbooks = pExcel->querySubObject("WorkBooks"); //获取工作簿
		pWorkbook = pWorkbooks->querySubObject("Open(QString, QVariant)",sXlsFile,QVariant(0)); //打开xls对应的工作簿
	}
	else
	{
		pWorkbooks = pExcel->querySubObject("WorkBooks");     //获取工作簿
		pWorkbooks->dynamicCall("Add");                       //添加一个新的工作薄
		pWorkbook  = pExcel->querySubObject("ActiveWorkBook"); //新建一个xls
	}

//    pWorksheet = pWorkbook->querySubObject("WorkSheets");
//    int sheetCount = pWorksheet->property("Count").toInt();

	pWorksheet = pWorkbook->querySubObject("WorkSheets(int)", nCurrSheet);//打开第一个sheet

	//至此已打开，开始获取相应属性
	QAxObject *usedrange = pWorksheet->querySubObject("UsedRange");//获取该sheet的使用范围对象
	QAxObject *rows = usedrange->querySubObject("Rows");
	QAxObject *columns = usedrange->querySubObject("Columns");

	//因为excel可以从任意行列填数据而不一定是从0,0开始，因此要获取首行列下标
	nStartRow    = usedrange->property("Row").toInt();    //第一行的起始位置
	nStartColumn = usedrange->property("Column").toInt(); //第一列的起始位置

	nRowCount    = rows->property("Count").toInt();       //获取行数
	nColumnCount = columns->property("Count").toInt();    //获取列数

	bIsOpen  = true;
	return bIsOpen;
}
/**
  *@brief Open()的重载函数
  */
bool ExcelEngine::Open(QString xlsFile, UINT nSheet, bool visible)
{
	sXlsFile = xlsFile;
	nCurrSheet = nSheet;
	bIsVisible = visible;

	return Open(nCurrSheet,bIsVisible);
}


bool ExcelEngine::OpenWithSheet(QString sheetName)
{
	bool visible = false;
//    if(sheetName.isEmpty())
//        Open(1,false);
//    else
	{
		if ( bIsOpen )
		{
			//return bIsOpen;
			Close();
		}

		nCurrSheet = 1;
		bIsVisible = visible;

		if ( NULL == pExcel )
		{
			pExcel = new QAxObject("Excel.Application");
			if ( pExcel )
			{
				bIsValid = true;
			}
			else
			{
				bIsValid = false;
				bIsOpen  = false;
				return bIsOpen;
			}

			pExcel->dynamicCall("SetVisible(bool)", bIsVisible);
		}

		if ( !bIsValid )
		{
			bIsOpen  = false;
			return bIsOpen;
		}

		if ( sXlsFile.isEmpty() )
		{
			bIsOpen  = false;
			return bIsOpen;
		}

		/*如果指向的文件不存在，则需要新建一个*/
		QFile f(sXlsFile);
		if (!f.exists())
		{
			bIsANewFile = true;
		}
		else
		{
			bIsANewFile = false;
		}

		if (!bIsANewFile)
		{
			pWorkbooks = pExcel->querySubObject("WorkBooks"); //获取工作簿
			pWorkbook = pWorkbooks->querySubObject("Open(QString, QVariant)",sXlsFile,QVariant(0)); //打开xls对应的工作簿
		}
		else
		{
			pWorkbooks = pExcel->querySubObject("WorkBooks");     //获取工作簿
			pWorkbooks->dynamicCall("Add");                       //添加一个新的工作薄
			pWorkbook  = pExcel->querySubObject("ActiveWorkBook"); //新建一个xls
		}

		//get count of sheet;
		if(!pWorkbook)
		{
			QMessageBox::critical(0,QStringLiteral("提示"),QStringLiteral("文件格式有错！"));
			return false;
		}
		pWorksheet = pWorkbook->querySubObject("WorkSheets");
		int countSheet = pWorksheet->property("Count").toInt();
		for(int i=1;i<=countSheet;i++){
			pWorksheet = pWorkbook->querySubObject("WorkSheets(int)", i);//打开r指定sheet
			QString sheetname = pWorksheet->property("Name").toString();
			if(QString::compare(sheetname,sheetName)==0){
				nCurrSheet = i;
				break;
			}
		}
		pWorksheet = pWorkbook->querySubObject("WorkSheets(int)", nCurrSheet);//打开r指定sheet

		//至此已打开，开始获取相应属性
		QAxObject *usedrange = pWorksheet->querySubObject("UsedRange");//获取该sheet的使用范围对象
		QAxObject *rows = usedrange->querySubObject("Rows");
		QAxObject *columns = usedrange->querySubObject("Columns");

		//因为excel可以从任意行列填数据而不一定是从0,0开始，因此要获取首行列下标
		nStartRow    = usedrange->property("Row").toInt();    //第一行的起始位置
		nStartColumn = usedrange->property("Column").toInt(); //第一列的起始位置

		nRowCount    = rows->property("Count").toInt();       //获取行数
		nColumnCount = columns->property("Count").toInt();    //获取列数

		bIsOpen  = true;
		return bIsOpen;
	}
}

/**
  *@brief 保存表格数据，把数据写入文件
  */
void ExcelEngine::Save()
{
	if ( pWorkbook )
	{
		if (bIsSaveAlready)
		{
			return ;
		}

		if (!bIsANewFile)
		{
			pWorkbook->dynamicCall("Save()");
		}
		else /*如果该文档是新建出来的，则使用另存为COM接口*/
		{
			pWorkbook->dynamicCall("SaveAs (const QString&,int,const QString&,const QString&,bool,bool)",
					  sXlsFile,56,QString(""),QString(""),false,false);

		}

		bIsSaveAlready = true;
	}
}

/**
  *@brief 关闭前先保存数据，然后关闭当前Excel COM对象，并释放内存
  */
void ExcelEngine::Close(bool IsToSave)
{
	if(IsToSave){
		//关闭前先保存数据
		Save();
	}

	if ( pExcel && pWorkbook )
	{
		if(pWorkbook)
		pWorkbook->dynamicCall("Close(bool)", true);
		if(pExcel)pExcel->dynamicCall("Quit()");
		if(pExcel)
		delete pExcel;
		pExcel = NULL;

		bIsOpen     = false;
		bIsValid    = false;
		bIsANewFile = false;
		bIsSaveAlready = true;
	}
	qDebug()<<QStringLiteral("Excel操作完成,关闭....")<<QStringLiteral("是否保存:")<<IsToSave;
	emit finishedReadData();
}

/**
  *@brief 把tableWidget中的数据保存到excel中
  *@param tableWidget : 指向GUI中的tablewidget指针
  *@return 保存成功与否 true : 成功
  *                  false: 失败
  */
bool ExcelEngine::SaveDataFrTable(QTableWidget *tableWidget)
{
	if ( NULL == tableWidget )
	{
		return false;
	}
	if ( !bIsOpen )
	{
		return false;
	}

	int tableR = tableWidget->rowCount();
	int tableC = tableWidget->columnCount();

	//获取表头写做第一行
	for (int i=0; i<tableC; i++)
	{
		if ( tableWidget->horizontalHeaderItem(i) != NULL )
		{
			this->SetCellData(1,i+1,tableWidget->horizontalHeaderItem(i)->text());
		}
	}

	//写数据
	for (int i=0; i<tableR; i++)
	{
		for (int j=0; j<tableC; j++)
		{
			if ( tableWidget->item(i,j) != NULL )
			{
				this->SetCellData(i+2,j+1,tableWidget->item(i,j)->text());
			}
		}
	}

	//保存
	Save();

	return true;
}

/**
  *@brief 从指定的xls文件中把数据导入到tableWidget中
  *@param tableWidget : 执行要导入到的tablewidget指针
  *@return 导入成功与否 true : 成功
  *                   false: 失败
  */
bool ExcelEngine::ReadDataToTable(QTableWidget *tableWidget)
{
	if ( NULL == tableWidget )
	{
		return false;
	}
	//先把table的内容清空
	int tableColumn = tableWidget->columnCount();
	tableWidget->clear();
	for (int n=0; n<tableColumn; n++)
	{
		tableWidget->removeColumn(0);
	}
	int rowcnt    = nStartRow + nRowCount;
	int columncnt = nStartColumn + nColumnCount;

	//获取excel中的第一行数据作为表头
	QStringList headerList;
	for (int n = nStartColumn; n<columncnt; n++ )
	{
		QAxObject * cell = pWorksheet->querySubObject("Cells(int,int)",nStartRow, n);
		if ( cell )
		{
			headerList<<cell->dynamicCall("Value2()").toString();
		}
	}

	//重新创建表头
	tableWidget->setColumnCount(nColumnCount);
	tableWidget->setHorizontalHeaderLabels(headerList);


	//插入新数据
	for (int i = nStartRow+1, r = 0; i < rowcnt; i++, r++ )  //行
	{
		tableWidget->insertRow(r); //插入新行
		for (int j = nStartColumn, c = 0; j < columncnt; j++, c++ )  //列
		{
			QAxObject * cell = pWorksheet->querySubObject("Cells(int,int)", i, j );//获取单元格

			//在r新行中添加子项数据
			if ( cell )
			{
				tableWidget->setItem(r,c,new QTableWidgetItem(cell->dynamicCall("Value2()").toString()));
			}
		}
	}

	return true;
}

/**
 *@brief 自定义函数:实现从Excel表中第一列数据进行查找,匹配第colum列数据,并更新tableWidget中已有数据
 * 没有的数据未作处理,直接忽略(可考虑没有的数据进行插入新行--需考虑该行是否可用)
 * @author vacant 379599039@vip.qq.com
 * @date 2014-8-17
 */
bool ExcelEngine::ReadDataReplace(QTableWidget *tableWidget, int colum)
{
	Open(1,false);
	if ( NULL == tableWidget )
	{
		return false;
	}

	int rowcnt    = nStartRow + nRowCount;
//    int columncnt = nStartColumn + nColumnCount;

	//插入新数据
	QMap<int,QStringList> mapelsData;
	for (int i = 0 /*+1*//*, r = 0*/; i < rowcnt; i++ )  //行
	{
		QAxObject *cellFirstcolumn = pWorksheet->querySubObject("Cells(int,int)",i,nStartColumn);//第i行第一列数据
		if(cellFirstcolumn){
//            int tableGroupNum = tableWidget->item((i-1)<0?0:(i-1),0)->text().toInt();
			int groupnumb = i;
			QAxObject *cellValue = pWorksheet->querySubObject("Cells(int,int)",groupnumb,colum+1);
			int numOfFirstColumn = cellFirstcolumn->dynamicCall("Value2()").toInt();
			if(cellValue)
				mapelsData.insert(numOfFirstColumn,QStringList()<<
									QString::number(numOfFirstColumn)<<
									QString::number(cellValue->dynamicCall("Value2()").toFloat()));
		}
	}

	//update the ui tablewidget data
	if(mapelsData.size())
	{
		foreach(QStringList value,mapelsData)
		{
			int groupnum = value.at(0).toInt();
			if(tableWidget->item(groupnum-1,colum)){
				QTableWidgetItem *item = new QTableWidgetItem(QString::number(value.at(1).toFloat()));
				item->setTextAlignment(Qt::AlignCenter);
				tableWidget->setItem( groupnum-1 , colum ,item);
			}
		}
	}
	Close(false);
	return true;
}

bool ExcelEngine::ReadDataSheet(QString sheetName, QString groupName)//读取单元格数据至sqlite中,以便后续数据操作，加快程序响应速度
{
	OpenWithSheet(sheetName);
	emit startReadData();

	int rowcnt    = nStartRow + nRowCount;
	int columncnt = nStartColumn + nColumnCount;

	QMap<QPair<int,int>,QString> cellDataMap;

	QSqlDatabase db=QSqlDatabase::addDatabase("QSQLITE");
	db.setDatabaseName(SQLDATANAME);
	QSqlQuery query;
	if(db.open()){
		query = QSqlQuery(db);
	}

	//获取excel中的第一行数据作为表头
	QStringList headerList;
	for (int n = nStartColumn; n<columncnt; n++ )
	{
		QAxObject * cell = pWorksheet->querySubObject("Cells(int,int)",nStartRow, n);
		if ( cell )
		{
			headerList<<cell->dynamicCall("Value2()").toString();
		}
	}

	//重新创建表头
	QString filedList ="";//= "'"+ headerList.first() + "' varchar(20),";
	foreach(QString filed,headerList){
		if(filed.contains(QStringLiteral("上午投喂单次称量标准")))
			filed = QStringLiteral("场次");
		if(headerList.last() != filed )
		   filedList.append("'"+ filed + "' varchar(50) ,");
		else filedList.append("'"+ filed + "' varchar(50)");
	}
	query.exec(QString("drop table %1").arg( groupName + "_" + sheetName));
	if(query.exec(QString("create table %2 ( %1 )").arg(filedList,groupName + "_" + sheetName))){
		qDebug()<<query.lastError();//<<QString("create table excel ( %1 )").arg(filedList);
	}

	//插入新数据
	for (int i = nStartRow+1, r = 0; i < rowcnt; i++, r++ )  //行
	{
		QString filedValues ="";
		for (int j = nStartColumn, c = 0; j < columncnt; j++, c++ )  //列
		{
			QAxObject * cell = pWorksheet->querySubObject("Cells(int,int)", i, j );//获取单元格
			//在r新行中添加子项数据
			if ( cell )
			{
				if(j == nStartColumn){//第1列
					if(i !=2)
						filedValues.append(" '"+cell->dynamicCall("Value2()").toString()+"' ");
					else
					{//第1列,第2行
						filedValues=(" '"+QStringLiteral("上午投喂单次称量标准")+"' ");
					}
				}
				else filedValues.append(", '"+cell->dynamicCall("Value2()").toString()+"' ");
			}
		}
		query.exec(QString("insert into %2 values(%1)").arg(filedValues,groupName + "_" + sheetName));
	}
	CurrentcellDataMap = cellDataMap;
	emit finishedReadData();
	return true;
}

//对应箱号与其值:根据系统时间自动判断上下午数据
QMap<int, QString> ExcelEngine::ReadDataByColumn(QString matchStr , QString sheetName)//
{
	matchStr = QDateTime::currentDateTime().toString("MMMdd");
//    Open(sheetName,false);
	ReadDataSheet(sheetName);

	int rowcnt    = nStartRow + nRowCount;
	int columncnt = nStartColumn + nColumnCount;////QDateTime::currentDateTime().toString("hh")
	if(QDateTime::currentDateTime().toString("hh").toInt()<12)//上午
		rowcnt = 26;
	else{
		nStartRow = 28;
		rowcnt = nStartRow + 26;
	}

	//插入新数据
	QMap<int,QString> cellDataMap;
	//插入新数据
	int findColumn = 3;
	foreach(QString value,CurrentcellDataMap){
		if(QString::compare(value,matchStr)==0)
		{
			findColumn = CurrentcellDataMap.key(value).second;
			break;
		}
	}

	for (int i = nStartRow+1, r = 0; i < rowcnt; i++, r++ )  //行
	{
		int columnNum;
		for (int j = nStartColumn, c = 0; j < columncnt; j++, c++ )  //列
		{
			QAxObject * cell =NULL;
			int columnStr;
			if(j==2){
				columnNum = j;
				cell= pWorksheet->querySubObject("Cells(int,int)", i, j );//获取单元格
				if(cell)
					columnStr = cell->dynamicCall("Value2()").toInt();
			}
			else if(j == findColumn){
				cell= pWorksheet->querySubObject("Cells(int,int)", i, j );//获取单元格
			}

			//在r新行中添加子项数据
			if ( cell )
			{
				cellDataMap.insert(columnStr,cell->dynamicCall("Value2()").toString());
//                tableWidget->setItem(r,c,new QTableWidgetItem(cell->dynamicCall("Value2()").toString()));
			}
		}
	}

//    foreach(QString value,cellDataMap){
//        qDebug()<<value<<cellDataMap.key(value);
//    }
	return cellDataMap;
}

QMap<int, QString> ExcelEngine::ReadDataFromSqlByColumn(bool flag_ReadType/*true->from excel,false->sql*/, QString sheetName,QString groupName)
{
	this->sheetName = sheetName;
	//excel start date
	QDateTime startDate(QDate(1900, 1, 1),QTime(0,0,0));
//    QDateTime endDate = QDateTime(QDate(2014, 7, 5),QTime(0,0,0));//QDateTime::currentDateTime();//(QDate(2013, 10, 1),QTime(0,0,0));
	QDateTime endDate = QDateTime::currentDateTime();//(QDate(2013, 10, 1),QTime(0,0,0));
	qint64 realedays = startDate.daysTo(endDate) +2;//the reale day.
	if(flag_ReadType){
		ReadDataSheet(sheetName,groupName);
		Close(false);
	}
//    Read from sql;
	QMap<int/*箱号*/, QString/*对应值*/> value_Box;// = QMap<int,QString>(0,QString(""));
	QSqlDatabase db=QSqlDatabase::addDatabase("QSQLITE");
	db.setDatabaseName(SQLDATANAME);
	QSqlQuery query;
	if(db.open()){
		query = QSqlQuery(db);
	}
	query.exec(QString("select * from %1").arg(groupName + "_" + sheetName));
	int filedOfMatchDay = -1;
	int addnum = 0;
	qDebug()<<"query:::"<<query.lastError();
	while(query.next())
	{
		++addnum;
		{
			QString valueOfDayAP;
			valueOfDayAP = QDateTime::currentDateTime().toString("ap");
			if(valueOfDayAP.contains("am"))
				valueOfDayAP = QStringLiteral("上午");
			else if(valueOfDayAP.contains("pm"))
					valueOfDayAP = QStringLiteral("下午");
			if(valueOfDayAP == QStringLiteral("上午")){
				if(addnum>26)
					break;
			}else{
				if(addnum<26)
					continue;
			}

			//获取列->
			if(filedOfMatchDay<0){
				for(int i=0;i<query.record().count();i++){
					if(query.record().value(i).toString().contains(QString::number(realedays))){
						filedOfMatchDay = i;
						break;
					}
				}
			}else{
//                qDebug()<<"...."<<query.record().field(query.record().indexOf(QStringLiteral("箱号"))).value().toInt();
				value_Box.insert(query.record().field(query.record().indexOf(QStringLiteral("箱号"))).value().toInt(),
								 query.record().field(filedOfMatchDay).value().toString());
			}
		}
	}
	foreach(QString value,value_Box){
		qDebug()<<__LINE__<<value<<value_Box.key(value);
	}
	return value_Box;
}

bool ExcelEngine::WriteDataToXls(QMap<int, QString> m_map, QString sheetName)
{
	OpenWithSheet(sheetName);
	QDateTime startDate(QDate(1900, 1, 1),QTime(0,0,0));
//    QDateTime endDate = QDateTime(QDate(2014, 7, 2),QTime(0,0,0));//QDateTime::currentDateTime();//(QDate(2013, 10, 1),QTime(0,0,0));
	QDateTime endDate = QDateTime::currentDateTime();//(QDate(2013, 10, 1),QTime(0,0,0));
	qint64 realedays = startDate.daysTo(endDate) + 2 ;//the reale day

	int rowcnt    = nStartRow + nRowCount;
	int columncnt = nStartColumn + nColumnCount;

	QMap<int,QString> cellDataMap;

	//区分上下午
	QString valueOfDayAP;
	valueOfDayAP = QDateTime::currentDateTime().toString("ap");
	if(valueOfDayAP.contains("am"))
		valueOfDayAP = QStringLiteral("上午");
	else if(valueOfDayAP.contains("pm"))
		valueOfDayAP = QStringLiteral("下午");
	//判断当前是上午还是下午
	bool flag_ap = true;//true->am.false->pm.
	if(valueOfDayAP == QStringLiteral("下午"))
		flag_ap =false;

	int rowOfStartWrite;
	int startRow=flag_ap?nStartRow:nStartRow+26;
	for(int i=startRow;i<rowcnt;i++){//直接跳到对应上下午所在行
		QString row_date;
		QAxObject * cell = NULL;
		cell= pWorksheet->querySubObject("Cells(int,int)", i, nStartColumn );//获取单元格
		if(cell){
			row_date = cell->dynamicCall("Value2()").toString();
			if(row_date.contains(valueOfDayAP)){
				rowOfStartWrite = i;
				break;//所在行的首列数据为空时表示该行不为开始行,直接跳到下一行
			}
		}
	}

	int columnNum_Date = 4;//默认为第四列
	int rowNum_Box,columnNum_Box = 3;//默认为第三列,若查找不到对应日期则从第三列开始插入数据

	for(int j = nStartColumn; j < columncnt; j++ ){//查找箱号对应的列及开始可写数据的行
		QAxObject * cell = NULL;
		cell= pWorksheet->querySubObject("Cells(int,int)", rowOfStartWrite, j );//获取单元格
		if(cell){
			if(cell->dynamicCall("Value2()").toString().contains(QStringLiteral("箱号"))){
				columnNum_Box = j;//find the field then find the row
				for(int i = rowOfStartWrite;i<rowcnt;i ++){
					cell= pWorksheet->querySubObject("Cells(int,int)", i, columnNum_Box );//获取单元格
					if(cell){
						if(cell->dynamicCall(("Value2()")).toInt()){
							rowNum_Box = i;
							break;
						}else{
							continue;
						}
					}
				}
				break;
			}
		}
	}
	//查找对应日期所在列
	for(int d_i = rowOfStartWrite;d_i<rowNum_Box;d_i++){
		QAxObject * cell = NULL;
		bool flag_break=false;
		for(int c_j = columnNum_Box;c_j<columncnt;c_j++){
			cell = pWorksheet->querySubObject("Cells(int,int)",d_i,c_j);
			if(cell){
				QString matchDate = cell->dynamicCall("Value2()").toString();
				if(matchDate.toInt()>0 && matchDate.toInt()<1000)
					break;
				if( matchDate == QString::number(realedays)){
					columnNum_Date = c_j;
					flag_break = true;
					break;
				}
			}
		}
		if(flag_break)
			break;
	}

	//从map里取出数据进行保存
	if(m_map.count()){
//        QMap<int,QString>::const_iterator itor;
//        for(itor = m_map.constBegin();itor!=m_map.constEnd();itor ++)
		QAxObject * cell = NULL;
		QVariant variant_ = "-";
		if(flag_ap)
			rowcnt = 27;
		for(int iN = rowNum_Box;iN<rowcnt;iN++){
			cell = pWorksheet->querySubObject("Cells(int,int)",iN,columnNum_Box);
			if(cell){
				int boxnum = cell->dynamicCall("Value2()").toInt();
				QMap<int,QString>::iterator itor = m_map.find(boxnum);
//                if(itor !=  QMap<int,QString>::iterator())
				if(itor !=  m_map.end())
					variant_ = itor.value();
				this->SetCellData(iN,columnNum_Date,variant_);
			}
		}
		return true;
	}else
		return false;
}

/**
  *@brief 获取指定单元格的数据
  *@param row : 单元格的行号
  *@param column : 单元格的列号
  *@return [row,column]单元格对应的数据
  */
QVariant ExcelEngine::GetCellData(UINT row, UINT column)
{
	QVariant data;

	QAxObject *cell = pWorksheet->querySubObject("Cells(int,int)",row,column);//获取单元格对象
	if ( cell )
	{
		data = cell->dynamicCall("Value2()");
	}

	return data;
}

/**
  *@brief 修改指定单元格的数据
  *@param row : 单元格的行号
  *@param column : 单元格指定的列号
  *@param data : 单元格要修改为的新数据
  *@return 修改是否成功 true : 成功
  *                   false: 失败
  */
bool ExcelEngine::SetCellData(UINT row, UINT column, QVariant data)
{
	bool op = false;

	QAxObject *cell = pWorksheet->querySubObject("Cells(int,int)",row,column);//获取单元格对象
	if ( cell )
	{
		QString strData = data.toString(); //excel 居然只能插入字符串和整型，浮点型无法插入
		cell->dynamicCall("SetValue(const QVariant&)",strData); //修改单元格的数据
		op = true;
	}
	else
	{
		op = false;
	}

	return op;
}

/**
  *@brief 清空除报表之外的数据
  */
void ExcelEngine::Clear()
{
	sXlsFile     = "";
	nRowCount    = 0;
	nColumnCount = 0;
	nStartRow    = 0;
	nStartColumn = 0;
}

/**
  *@brief 判断excel是否已被打开
  *@return true : 已打开
  *        false: 未打开
  */
bool ExcelEngine::IsOpen()
{
	return bIsOpen;
}

/**
  *@brief 判断excel COM对象是否调用成功，excel是否可用
  *@return true : 可用
  *        false: 不可用
  */
bool ExcelEngine::IsValid()
{
	return bIsValid;
}

/**
  *@brief 获取excel的行数
  */
UINT ExcelEngine::GetRowCount()const
{
	return nRowCount;
}

/**
  *@brief 获取excel的列数
  */
UINT ExcelEngine::GetColumnCount()const
{
	return nColumnCount;
}
